package com.ssi.mrms.dao.Impl;

import static java.text.MessageFormat.format;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.ssi.mrms.dao.UserDao;
import com.ssi.mrms.dao.mapping.RoleMapping;
import com.ssi.mrms.dao.mapping.UserMapping;
import com.ssi.mrms.model.DataTableModel;
import com.ssi.mrms.model.Role;
import com.ssi.mrms.model.User;
import com.ssi.mrms.security.CustomPasswordEncoder;
import com.ssi.mrms.utils.SecurityUtils;

@Repository
public class UserDaoImpl implements UserDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    private static final Logger logger = Logger.getLogger(UserDaoImpl.class);

    private static final String SQL_QUERY = "SELECT * FROM USR WHERE 1=1  ";
    private static final String SQL_QUERY_BY_ID = "SELECT * FROM USR WHERE USRID = ?";
    private static final String SQL_QUERY_BY_USERNAME = "SELECT * FROM USR WHERE USRNME = ?";
    private static final String SQL_QUERY_BY_LOGIN_USERNAME = "SELECT * FROM USR WHERE USRNME = ? AND ACTIVE = 'Y' ";
    private static final String SQL_QUERY_ROLE = " SELECT * FROM ROLE , USRROLE WHERE  USRROLE.ROLID = ROLE.ROLID AND USRID = ? ";

    @Override
    public List<User> findAll() {
        List<User> lists = jdbcTemplate.query(SQL_QUERY, new Object[]{}, new UserMapping());
        if (lists == null || lists.isEmpty()) {
            lists = new ArrayList<>();
        } else {
            for (User user : lists) {
                List<Role> roles = new ArrayList<>();
                roles = jdbcTemplate.query(SQL_QUERY_ROLE, new Object[]{user.getUserId()}, new RoleMapping());
                user.setRoles(roles);
            }
        }
        return lists;
    }

    @Override
    public List<User> findUserByCriteria(User user) {

        if (StringUtils.isNotBlank(user.getUserName())) {
            SQL_QUERY.concat(" AND USRNME LIKE '%" + user.getUserName() + "%' ");
        }
        if (StringUtils.isNotBlank(user.getFirstName())) {
            SQL_QUERY.concat(" AND FNAME LIKE '%" + user.getFirstName() + "%' ");
        }
        if (StringUtils.isNotBlank(user.getLastName())) {
            SQL_QUERY.concat(" AND LNAME LIKE '%" + user.getLastName() + "%' ");
        }
        if (StringUtils.isNotBlank(user.getEmail())) {
            SQL_QUERY.concat(" AND USRNME LIKE '%" + user.getEmail() + "%' ");
        }
        if (StringUtils.isNotBlank(user.getRole())) {
            SQL_QUERY.concat(" AND USRID IN (SELECT USRID FROM USRROLE WHERE ROLID =" + user.getRole() + ") ");
        }
        if (StringUtils.isNotBlank(user.getActive())) {
            SQL_QUERY.concat(" AND ACTIVE = '" + user.getActive() + "' ");
        }

        logger.info("## findUserByCriteria SQL: " + SQL_QUERY);

        List<User> lists = jdbcTemplate.query(SQL_QUERY, new Object[]{}, new UserMapping());

        if (lists == null || lists.isEmpty()) {
            lists = new ArrayList<>();
        } else {
            for (User u : lists) {
                List<Role> roles = new ArrayList<>();
                roles = jdbcTemplate.query(SQL_QUERY_ROLE, new Object[]{u.getUserId()}, new RoleMapping());
                u.setRoles(roles);
            }
        }
        return lists;
    }

    @Override
    public User findById(Integer id) {
        User model = null;
        List<User> lists = jdbcTemplate.query(SQL_QUERY_BY_ID, new Object[]{id}, new UserMapping());
        if (lists != null && lists.size() == 1) {
            model = lists.get(0);
        }
        return model;
    }

    @Override
    public User findByUsername(String username) {
        User model = null;
        List<User> users = jdbcTemplate.query(SQL_QUERY_BY_USERNAME, new Object[]{username}, new UserMapping());
        if (users != null && users.size() == 1) {
            model = users.get(0);
            List<Role> roles = jdbcTemplate.query(SQL_QUERY_ROLE, new Object[]{model.getUserId()}, new RoleMapping());
            model.setRoles(roles);
            if (roles != null && roles.size() == 1) {
            	model.setUserRole(roles.get(0));
            }
        }
        return model;
    }
    
    @Override
    public User findLoginUsername(String username) {
    	User model = null;
    	List<User> users = jdbcTemplate.query(SQL_QUERY_BY_LOGIN_USERNAME, new Object[]{username}, new UserMapping());
    	if (users != null && users.size() == 1) {
    		model = users.get(0);
    		List<Role> roles = jdbcTemplate.query(SQL_QUERY_ROLE, new Object[]{model.getUserId()}, new RoleMapping());
    		model.setRoles(roles);
    	}
    	return model;
    }

    @Override
    public DataTableModel<User> findUserDataTable(User user, int page, int perPage) {

    	DataTableModel<User> dataTableModel = new DataTableModel<User>();

        String sql_where = "";
        if (StringUtils.isNotBlank(user.getUserName())) {
            sql_where = sql_where.concat(" AND USRNME LIKE '%" + user.getUserName() + "%' ");
        }
        if (StringUtils.isNotBlank(user.getFirstName())) {
            sql_where = sql_where.concat(" AND FNAME LIKE '%" + user.getFirstName() + "%' ");
        }
        if (StringUtils.isNotBlank(user.getLastName())) {
            sql_where = sql_where.concat(" AND LNAME LIKE '%" + user.getLastName() + "%' ");
        }
        if (StringUtils.isNotBlank(user.getEmail())) {
            sql_where = sql_where.concat(" AND USRNME LIKE '%" + user.getEmail() + "%' ");
        }
        if (StringUtils.isNotBlank(user.getRole())) {
            sql_where = sql_where.concat(" AND USRID IN (SELECT USRID FROM USRROLE WHERE ROLID =" + user.getRole() + ") ");
        }
        if (StringUtils.isNotBlank(user.getActive())) {
            sql_where = sql_where.concat(" AND ACTIVE = '" + user.getActive() + "' ");
        }

        String sqlLimit = MessageFormat.format(" limit {0},{1} ", new Object[]{page, perPage});

        String sql_query = SQL_QUERY + sql_where + sqlLimit;

        logger.info("## findUserByCriteria SQL: " + sql_query);

        List<User> lists = jdbcTemplate.query(sql_query, new Object[]{}, new UserMapping());
        if (lists == null || lists.isEmpty()) {
            lists = new ArrayList<>();
        } else {
            for (User u : lists) {
                List<Role> roles = new ArrayList<>();
                roles = jdbcTemplate.query(SQL_QUERY_ROLE, new Object[]{u.getUserId()}, new RoleMapping());
                u.setRoles(roles);
            }
        }

        // ------ For Count Rows -----
        String sqlCount = " SELECT COUNT(1) AS TOTAL FROM USR WHERE 1=1 " + sql_where;
        Integer count = jdbcTemplate.query(sqlCount, new ResultSetExtractor<Integer>() {
            @Override
            public Integer extractData(java.sql.ResultSet rs) throws SQLException, DataAccessException {
                if (rs.next()) {
                    return rs.getInt("total");
                }
                return null;
            }
        });

        dataTableModel.setItems(lists);
        dataTableModel.setTotal(count);
        dataTableModel.setIndex(page);
        dataTableModel.setLength(perPage);
        return dataTableModel;
    }

	private static final String SQL_INSERT_USER = "insert into usr" +
			" (usrnme, usrpsw, active, creby, credat, updby, updat, fname, lname, email)" +
			" values(?, ?, ?, ?,  CURDATE() , ?,  CURDATE(), ?, ?, ?) ";

    @Override
    public void create(final User user) {

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(format(SQL_INSERT_USER), Statement.RETURN_GENERATED_KEYS);
                int i = 0;
                ps.setString(++i, user.getUserName() == null ? null : user.getUserName());
                ps.setString(++i, CustomPasswordEncoder.encode(user.getPassword()));
                ps.setString(++i, user.getActive() == null ? null : user.getActive());
                ps.setString(++i, SecurityUtils.getUsername());
                ps.setString(++i, SecurityUtils.getUsername());
                ps.setString(++i, user.getFirstName() == null ? null : user.getFirstName());
                ps.setString(++i, user.getLastName() == null ? null : user.getLastName());
                ps.setString(++i, user.getEmail() == null ? null : user.getEmail());
                return ps;
            }

        }, keyHolder);
        
    	String insert_role = " INSERT INTO `mrms`.`usrrole` ( USRID, ROLID, CREBY, CREDAT, UPDBY, UPDAT) " +
    			" VALUES ( ?, ?, ?, CURDATE() ,? , CURDATE() ) ";
		
    	Integer userId = keyHolder.getKey().intValue();
		jdbcTemplate.update(insert_role,
				new Object[]{
			userId,
			user.getRole(),
			SecurityUtils.getUsername(),
			SecurityUtils.getUsername()
		});
        
        
    }

    @Override
    public void update(User user) {
    	
    	if(StringUtils.isBlank(user.getPassword())){
    		
    		String update_sql = " UPDATE USR SET  ACTIVE=?, " +
    				" UPDBY=?, UPDAT=CURDATE(), FNAME=?, LNAME=?, EMAIL=? WHERE USRID=? ";
    		  
    		jdbcTemplate.update(update_sql,
	                new Object[]{
	                    user.getActive(),
	                    SecurityUtils.getUsername(),
	                    user.getFirstName(),
	                    user.getLastName(),
	                    user.getEmail(),
	                    user.getUserId()
	                });
    		
    		String update_role = " UPDATE `mrms`.`usrrole` SET ROLID=?,  UPDBY=?, UPDAT=CURDATE() WHERE USRID=? ";
    		
    		jdbcTemplate.update(update_role,
    				new Object[]{
    			user.getRole(),
    			SecurityUtils.getUsername(),
    			user.getUserId()
    		});
    	}else{
    		
    		String update_sql = " UPDATE USR SET  USRPSW=?, ACTIVE=?, " +
    				" UPDBY=?, UPDAT=CURDATE(), FNAME=?, LNAME=?, EMAIL=? WHERE USRID=? ";
    		  
    		jdbcTemplate.update(format(update_sql),
    	                new Object[]{
    	                    CustomPasswordEncoder.encode(user.getPassword()),
    	                    user.getActive(),
    	                    SecurityUtils.getUsername(),
    	                    user.getFirstName(),
    	                    user.getLastName(),
    	                    user.getEmail(),
    	                    user.getUserId()
    	                });
    		
    		String update_role = " UPDATE `mrms`.`usrrole` SET ROLID=?,  UPDBY=?, UPDAT=CURDATE() WHERE USRID=? ";
    		
    		jdbcTemplate.update(format(update_role),
    				new Object[]{
    			user.getRole(),
    			SecurityUtils.getUsername(),
    			user.getUserId()
    		});
    	}

     
    }


    @Override
    public void delete(User user) {
    	String SQL_DELETE_USRNME = "DELETE FROM usr WHERE USRNME=?";
    	jdbcTemplate.update(format(SQL_DELETE_USRNME), new Object[]{user.getUserName()});
    }


}
